/* CRIANDO DATABASES */

CREATE DATABASE AULA01
GO

/* CONECTANDO A DATABASE */

USE AULA01
GO

/* RELACIONAMENTOS */

CREATE TABLE ALUNOS(
	NOME VARCHAR(30),
	EMAIL VARCHAR(30),
	SEXO CHAR(1),
	TIPO CHAR(3),
	NUMERO CHAR(10),
	ENDERECO VARCHAR(50)
)
GO

/* SCHEMA - DATABASE OWNER */

INSERT INTO ALUNOS VALUES('ADRIANA','ADRIANA@GMAIL.COM','F',
'CEL','77568908','RUA A, FLAMENGO - RJ')
INSERT INTO ALUNOS VALUES('ALESSANDRA','ALESSANDRA@GMAIL.COM','F',
'CEL','88790087','RUA B, COPACABANA - RJ')
GO

SELECT * FROM ALUNOS 
GO

INSERT INTO ALUNOS VALUES('JONAS','JONAS@GMAIL.COM','M',
'COM','89086757','RUA D, BOTAFOGO - RJ')
INSERT INTO ALUNOS VALUES('ADRIANA','ADRIANA@GMAIL.COM','F',
'COM','86954756','RUA A, FLAMENGO - RJ')
GO

SELECT SEXO, COUNT(*)
FROM ALUNOS
GROUP BY SEXO
GO

/* FEELING, EXPERIENCIA
FORMAS NORMAIS
1 - ATOMICIDADE, TODO ATRIBUTO DEVE SER ATOMO, INDIVISIVEL.
QUALQUER ATRIBUTO DIVISIVEL, ESSE CAMPO PROVAVELMENTE SE 
TORNARIA UMA OUTRA TABELA.
CAMPOS QUE POSSAM SE TORNAR UM VETOR, SERAO ARMAZENADOS EM
OUTRA TABELA
2 - ATRIBUTOS UNICOS - TODA TABELA DEVE TER PELO MENOS
UM CAMPO QUE IDENTIFIQUE TODO O REGISTRO COMO SENDO UNICO
(CHAVE PRIMARIA)
*/

INSERT INTO ALUNOS VALUES('ADRIANA','ADRIANA@GMAIL.COM','F',
'COM','86954756','RUA A, FLAMENGO - RJ')
GO

/* FASES DE MODELAGEM
CONCEITUAL - RASCUNHO
LOGICA - PROGRAMAS DE MODELAGEM
FISICA - SCRIPT DO BANCO
*/

CREATE TABLE ALUNO(
	IDALUNO INT PRIMARY KEY IDENTITY,
	NOME VARCHAR(30) NOT NULL,
	SEXO CHAR(1) NOT NULL,
	NASCIMENTO DATE NOT NULL,
	EMAIL VARCHAR(30) UNIQUE,
)
GO

/* CONSTRAINT - SAO REGRAS DE TABELA, COMO POR EXEMPLO
CHECAR VALORES  */

ALTER TABLE ALUNO ADD CONSTRAINT CK_SEXO
CHECK (SEXO IN('M','F'))
GO

ALTER TABLE ALUNO ADD CONSTRAINT PK_ALUNO
PRIMARY KEY(IDALUNO)
GO

/* CRIANDO A TABELA ENDERECO */

CREATE TABLE ENDERECO(
	IDENDERECO INT IDENTITY(100,10),
	BAIRRO VARCHAR(30) NOT NULL,
	UF CHAR(2) NOT NULL
	CHECK (UF IN('SP','RJ','MG')),
	ID_ALUNO INT UNIQUE
)
GO

/*CRIANDO A CHAVE PRIMARIA POR CONSTRAINT*/
ALTER TABLE ENDERECO ADD CONSTRAINT PK_ENDERECO
PRIMARY KEY(IDENDERECO)
GO
/*CRIANDO A CHAVE ESTRANGEIRA POR CONSTRAINT*/
ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDERECO_ALUNO
FOREIGN KEY(ID_ALUNO) REFERENCES ALUNO(IDALUNO)
GO

/* VERIFICANDO A CONFIGURACAO DAS TABELAS */

SP_COLUMNS ALUNO
GO

SP_HELP ALUNO /*ATALHO - SELECIONE O OBJETO e PRESSIONE
ALT + F1 */
GO

/* COLLATION - LATIN 1 - CONJUNTO DE CARACTERES DOS
PAISES LATINO AMERICANOS
O COLLATION PODE SER DEFINIDO EM NIVEL DE DATABASE
EM NIVEL DE TABELA E EM NIVEL DE COLUNA 
CS - CASE SENSITIVE CI - CASE INSENSITIVE
AS - ACENT SENSITIVE AI - ACENT INSENSITIVE
*/

INSERT INTO ALUNO VALUES('ANDRE','M','1981/12/20','ANDRE@GMAIL.COM')
INSERT INTO ALUNO VALUES('JOANA','F','1983/11/05','AJOANA@GMAIL.COM')
INSERT INTO ALUNO VALUES('MARIO','M','1979/10/20','MARIO@GMAIL.COM')
INSERT INTO ALUNO VALUES('RODRIGO','M','1980/09/20','RODRIGO@GMAIL.COM')
INSERT INTO ALUNO VALUES('GABRIEL','M','1983/12/22','GABRIEL@GMAIL.COM')
INSERT INTO ALUNO VALUES('AMANDA','F','1987/02/15','AMANDA@GMAIL.COM')
INSERT INTO ALUNO VALUES('JANAINA','F','1991/12/13','JANAINA@GMAIL.COM')

INSERT INTO ENDERECO VALUES('FLAMENGO','RJ',1)
INSERT INTO ENDERECO VALUES('BOTAFOGO','RJ',2)
INSERT INTO ENDERECO VALUES('FLAMENGO','RJ',3)
INSERT INTO ENDERECO VALUES('CENTRO','SP',4)
INSERT INTO ENDERECO VALUES('MORUMBI','SP',5)
INSERT INTO ENDERECO VALUES('PORTUARIO','MG',6)
INSERT INTO ENDERECO VALUES('CAMPO GRANDE','RJ',7)

SELECT * FROM ALUNO
SELECT * FROM ENDERECO 
GO

DROP TABLE TELEFONE
GO

CREATE TABLE TELEFONE(
	IDTELEFONE INT PRIMARY KEY IDENTITY,
	TIPO CHAR(3) NOT NULL,
	NUMERO VARCHAR(10) NOT NULL,
	ID_ALUNO INT,
	CHECK (TIPO IN('CEL','RES','COM'))
)
GO

INSERT INTO TELEFONE VALUES('CEL','67689909',1)
INSERT INTO TELEFONE VALUES('COM','88556956',1)
INSERT INTO TELEFONE VALUES('CEL','99555668',2)
INSERT INTO TELEFONE VALUES('COM','45956565',2)
INSERT INTO TELEFONE VALUES('COM','96658471',1)
INSERT INTO TELEFONE VALUES('CEL','96548523',1)
INSERT INTO TELEFONE VALUES('RES','36524589',4)
INSERT INTO TELEFONE VALUES('CEL','54845541',1)
INSERT INTO TELEFONE VALUES('COM','74847844',3)
INSERT INTO TELEFONE VALUES('CEL','66523548',2)
INSERT INTO TELEFONE VALUES('COM','88455863',6)
INSERT INTO TELEFONE VALUES('COM','99874563',5)
INSERT INTO TELEFONE VALUES('CEL','99874563',4)
INSERT INTO TELEFONE VALUES('RES','32547893',4)

ALTER TABLE TELEFONE ADD CONSTRAINT FK_TELEFONE_ALUNO
FOREIGN KEY(ID_ALUNO) REFERENCES ALUNO(IDALUNO)
ON DELETE SET NULL
GO

/* SELECAO, PROJECAO E JUNCAO */

SELECT GETDATE(), 10, 10+10 /*PROJECAO*/
GO

SELECT GETDATE() AS "HOJE", 10 AS "NUMERO", 
10 + 10 AS "SOMA"

SELECT NOME, GETDATE(), 10 + 10
FROM ALUNO
GO

/*SELECAO*/

SELECT NOME, GETDATE(), 10 + 10
FROM ALUNO
WHERE SEXO = 'F'
GO


SELECT NOME, TIPO, NUMERO /*PROJECAO*/
FROM ALUNO
INNER JOIN TELEFONE /*JUNCAO*/
ON IDALUNO = ID_ALUNO 
WHERE TIPO = 'CEL' /*SELECAO*/
GO

SELECT NOME, TIPO, NUMERO
FROM ALUNO, TELEFONE
WHERE IDALUNO = ID_ALUNO
GO

SELECT ALUNO.NOME, TELEFONE.TIPO, 
TELEFONE.NUMERO, ENDERECO.BAIRRO, ENDERECO.UF
FROM ALUNO
INNER JOIN TELEFONE
ON ALUNO.IDALUNO = TELEFONE.ID_ALUNO
INNER JOIN ENDERECO
ON ALUNO.IDALUNO = ENDERECO.ID_ALUNO
GO

SELECT A.NOME, T.TIPO, 
T.NUMERO, E.BAIRRO, E.UF
FROM ALUNO A INNER JOIN TELEFONE T 
ON A.IDALUNO = T.ID_ALUNO 
LEFT JOIN ENDERECO E 
ON A.IDALUNO = E.ID_ALUNO
GO

SELECT NOME, TIPO, NUMERO
FROM ALUNO A  RIGHT JOIN TELEFONE E
ON IDALUNO = ID_ALUNO
GO

SELECT NOME AS "ALUNO", 
	   ISNULL(TIPO, '*') AS "TIPO", 
	   ISNULL(NUMERO, 'SEM NUMERO') AS "TELEFONE"
FROM ALUNO A  LEFT JOIN TELEFONE E
ON IDALUNO = ID_ALUNO
GO




















































































































